//
//  DataBaseHandle.m
//  
//
//  Created by dllo on 16/1/11.
//
//

#import "DataBaseHandle.h"
#import <sqlite3.h>
#import "ModelForManage2.h"

@implementation DataBaseHandle

#pragma mark -  单例

+ (instancetype)shareDataBaseHandle {

    // 在静态区,只初始化一次
   static DataBaseHandle *dataBase = nil;
    
    if (dataBase == nil) {
        
        dataBase = [[DataBaseHandle alloc] init];
        
    }
    
    return dataBase;

}

sqlite3 *db;

#pragma mark -  打开数据库

//定义 SQLite 结构体(对象)

- (void)open {
    
    // API sqlite3_open
    
    if (db) {
       // NSLog(@"数据库已经开启!!!");
        return;
    }
    
    /** 
     * @param filename 数据库文件的路径(注意:类型为 C 字符串)
     *
     * @param ppDb 数据库对象
     */
    
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject] stringByAppendingPathComponent:@"Sport.sqlite"];
    
   // NSLog(@"%@", file);
    
//    sqlite3_open(<#const char *filename#>, <#sqlite3 **ppDb#>);
    
   int result = sqlite3_open(file.UTF8String, &db);  //utf8string 将字符串反转为 C 字符串
    if (result == SQLITE_OK) {
        
       // NSLog(@"数据库打开成功");
        
    } else {

        NSLog(@"数据库打开失败, code = %d", result);
    
    
    }
    

}

#pragma mark -  创建表格

- (void) createTable {

// API sqlite3_exec
    
    //创建 SQL 语句
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS sportApp(number INTEGER PRIMARY KEY AUTOINCREMENT, team text, teamLog text, count text, type text)";
    
//  NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS lanou1130(keyid INTEGER PRIMARY KEY AUTOINCREMENT,name TEXT,sex TEXT,age TEXT,number INTEGER)";
    
//    sqlite3_exec(<#sqlite3 *#>, <#const char *sql#>, <#int (*callback)(void *, int, char **, char **)#>, <#void *#>, <#char **errmsg#>);
    
    
 int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);

    if (result == SQLITE_OK) {
      //  NSLog(@"创建表格成功");
        
    }else {
    
        NSLog(@"创建表格失败. code:%d", result);
    
    }

}

#pragma mark -  插入数据

- (void)insertModel:(ModelForManage2 *) model {

// API: sqlite3_exec
    
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into sportApp(team, teamlog, count, type) VALUES('%@', '%@', '%@', '%@')", model.name_cn, model.logo_big, model.sl_id, model.LeagueType];
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
       // NSLog(@"添加数据完成");
    }else {

        NSLog(@"添加数据失败 code%d", result);
    
    }


}
#pragma mark - 删除数据

- (void)deleteWithModel:(ModelForManage2 *)model {

    //API sqlite3_exec
    
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM sportApp WHERE team = '%@'", model.name_cn];
    
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    
    if (result == SQLITE_OK) {
       // NSLog(@"删除成功");
    }else {
    
        NSLog(@"删除失败 code:%d", result);
    
    }
}

#pragma mark -  查询数据

- (NSArray *)select {

    //存放符合条件的数据(元素是 model 对象 )
    NSMutableArray *array = [NSMutableArray array];

    // sql 语句
    
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM sportApp"];
    
    // API stmt 结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column
    
    // 声明一个结构体 stmt 对象
    sqlite3_stmt *stmt = nil;
    
//    sqlite3_prepare_v2(<#sqlite3 *db#>, <#const char *zSql#>, <#int nByte#>, <#sqlite3_stmt **ppStmt#>, <#const char **pzTail#>);
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil); // -1 查询所有对象
    
    if (result == SQLITE_OK) {
        
        //NSLog(@"准备就绪, 查询中...");
             // 每行都执行查询语句
        
        while(sqlite3_step(stmt) == SQLITE_ROW) {
        
        // 如果查询条件匹配, 通过 sqlite3_column 函数簇  取出值.
            
            // 取出哪一列的数据
          const unsigned char *name = sqlite3_column_text(stmt, 1);
          const unsigned char *nameLogo = sqlite3_column_text(stmt, 2);
            const unsigned char *number = sqlite3_column_text(stmt, 3);
            const unsigned char *type = sqlite3_column_text(stmt, 4);

         // int age = sqlite3_column_int(stmt, 3);
            
           //创建 model 对象,赋值之后放入数组中
            
           ModelForManage2 *model = [[ModelForManage2 alloc] init];
            
           model.name_cn = [NSString stringWithUTF8String:(const char *) name];
            model.logo_big = [NSString stringWithUTF8String:(const char *) nameLogo];
            model.sl_id = [NSString stringWithUTF8String:(const char *) number];
            model.LeagueType = [NSString stringWithUTF8String:(const char *) type];
           // stu.age = age;
            
            [array addObject:model];
            
            [model release];
            
        }
        
        //销毁对象
        sqlite3_finalize(stmt);
        
    }else {
    
        NSLog(@"不能正常查询 code %d", result);
        
        //销毁对象
        sqlite3_finalize(stmt);
    }
      return array;
}

//#pragma mark -  关掉数据库
//
//- (void)closeDB {
//
////API sqlite3_close
//
//    int result = sqlite3_close(db);
//    
//    if (result == SQLITE_OK) {
//        NSLog(@"数据库关闭");
//        db = nil;
//    }else {
//        NSLog(@"数据库关闭失败 code:%d", result);
//    }
//}

@end
